package com.zatech.cgnci.project.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zatech.cgnci.project.base.context.UserContext;
import com.zatech.cgnci.project.base.enums.*;
import com.zatech.cgnci.project.base.exception.GlobalException;
import com.zatech.cgnci.project.base.utils.BeanUtils;
import com.zatech.cgnci.project.base.utils.ConvertUtils;
import com.zatech.cgnci.project.base.utils.DateUtils;
import com.zatech.cgnci.project.base.utils.VerifyUtils;
import com.zatech.cgnci.project.dto.CgnciUnearnedPremiumReserveLedgerDTO;
import com.zatech.cgnci.project.dto.ProvisionRegisterExtractDTO;
import com.zatech.cgnci.project.dto.ProvisionRegisterQueryDTO;
import com.zatech.cgnci.project.entity.*;
import com.zatech.cgnci.project.mapper.*;
import com.zatech.cgnci.project.service.*;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zatech.cgnci.project.vo.*;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 准备金台账管理表 服务实现类
 * </p>
 *
 * @author Generator
 * @since 2025-01-22
 */
@Service
public class CgnciProvisionRegisterServiceImpl extends ServiceImpl<CgnciProvisionRegisterMapper, CgnciProvisionRegisterDO> implements CgnciProvisionRegisterService {

    @Resource
    private CgnciOutstandingClaimsReserveLedgerMapper outstandingClaimsReserveLedgerMapper;
    @Resource
    private CgnciBusinessInsuranceMapper insuranceMapper;
    @Resource
    private CgnciUnearnedPremiumReserveLedgerMapper unearnedPremiumReserveLedgerMapper;
    @Resource
    private CgnciReinsuranceRiskInsuredService cgnciReinsuranceRiskInsuredService;
    @Resource
    private CgnciReinsuranceRiskService cgnciReinsuranceRiskService;
    @Resource
    private CgnciReinsuranceRiskLayerService cgnciReinsuranceRiskLayerService;
    @Resource
    private CgnciReinsurancePremiumBillMapper premiumBillMapper;
    @Resource
    private CgnciContractInvoiceMapper invoiceMapper;
    @Resource
    private CgnciReinsuranceInfoMapper infoMapper;
    @Resource
    private CgnciClaimSettlementService claimSettlementService;
    @Resource
    private CgnciEndorsementMapper endorsementMapper;
    @Resource
    private CgnciBusinessInsuranceService businessInsuranceService;
    @Resource
    CgnciClaimSettlementDetailMapper claimSettlementDetailMapper;
    @Resource
    private CgnciBillInfoService billInfoService;
    @Resource
    private CgnciReinsuranceInfoService cgnciReinsuranceInfoService;
    @Resource
    private CgnciUnearnedPremiumReserveLedgerService reserveLedgerService;
    @Resource
    private CgnciOutstandingClaimsReserveLedgerService  outstandingClaimsService;
    @Resource
    private CgnciBusinessProjectMapper projectMapper;
    @Resource
    private CgnciClaimPolicyMapper claimPolicyMapper;
    @Resource
    private CgnciClaimReserveDetailMapper reserveDetailMapper;
    @Resource
    private CgnciOutstandingClaimsReserveMapper claimsReserveMapper;
    private final static String OUTSTANDING = "1";
    private final static String UNEARNED = "2";

    @Override
    public IPage<CgnciProvisionRegisterVO> page(ProvisionRegisterQueryDTO queryDTO) {
        return this.baseMapper.pageAll(queryDTO.toPage(), queryDTO);
    }

    @Override
    @Transactional
    public void extract(ProvisionRegisterExtractDTO extractDTO) {
        UserEntity user = UserContext.getUser();
        CgnciProvisionRegisterDO registerDO  = new CgnciProvisionRegisterDO();
        registerDO.setControlsDate(new Date());
        registerDO.setReserveType(extractDTO.getReserveTypeCode());
        registerDO.setAsOfDate(extractDTO.getValuationYear()+"."+extractDTO.getValuationMonth());
        registerDO.setInitialDate(extractDTO.getValuationYear()+"/1/1");
        registerDO.setPeriodDate(DateUtils.getEndMonth(extractDTO.getValuationYear()+"/"+extractDTO.getValuationMonth()));
        extractDTO.setValuationYear(registerDO.getInitialDate());//返回完整期初日期
        extractDTO.setValuationMonth(registerDO.getPeriodDate());//返回完整期末日期
        registerDO.setCreateTime(new Date());
        registerDO.setCreator(user.getNameAndCode());
        CgnciProvisionRegisterDO provisionRegisterDO =  this.baseMapper.selectOne(new LambdaQueryWrapper<CgnciProvisionRegisterDO>()
                .eq(CgnciProvisionRegisterDO::getReserveType,extractDTO.getReserveTypeCode())
                .eq(CgnciProvisionRegisterDO::getAsOfDate,registerDO.getAsOfDate()));
        if(Objects.nonNull(provisionRegisterDO)){
            //先删除再新增
            this.baseMapper.delete(new LambdaQueryWrapper<CgnciProvisionRegisterDO>()
                    .eq(CgnciProvisionRegisterDO::getReserveType,extractDTO.getReserveTypeCode())
                    .eq(CgnciProvisionRegisterDO::getAsOfDate,registerDO.getAsOfDate()));
            //删除未到期准备金台账表
            reserveLedgerService.remove(new LambdaQueryWrapper<CgnciUnearnedPremiumReserveLedgerDO>()
                    .eq(CgnciUnearnedPremiumReserveLedgerDO::getRegisterId,provisionRegisterDO.getRegisterId()));
            //删除未决赔款准备金台账表
            outstandingClaimsService.remove(new LambdaQueryWrapper<CgnciOutstandingClaimsReserveLedgerDO>()
                    .eq(CgnciOutstandingClaimsReserveLedgerDO::getRegisterId,provisionRegisterDO.getRegisterId()));
        }
        this.baseMapper.insert(registerDO);
        extractDTO.setRegisterId(registerDO.getRegisterId());
        if(ReserveTypeEnum.OUTSTANDING.getCode().equals(extractDTO.getReserveTypeCode())){
            this.outstanding(extractDTO);
        }else if(ReserveTypeEnum.UNEARNED.getCode().equals(extractDTO.getReserveTypeCode())){
            this.unearned(extractDTO);
        }
    }

    /**
     * 未决赔款准备金
     * @param extractDTO
     */
    private void outstanding(ProvisionRegisterExtractDTO extractDTO) {
     //查询所有生效项目
        List<CgnciBusinessProjectDO> projectDOS = projectMapper.selectList(new LambdaQueryWrapper<CgnciBusinessProjectDO>()
                .eq(CgnciBusinessProjectDO::getStatus, '3')
                .eq(CgnciBusinessProjectDO::getIsMirror, '0'));
        List<CgnciOutstandingClaimsReserveLedgerDO> reserveLedgerDOS = new ArrayList<>();
        projectDOS.forEach(projectDO->{
            //查询项目下已创建生效的立案数据
            List<CgnciClaimPolicyVO> projectVO = claimPolicyMapper.getPolicyByProjectNo(projectDO.getProjectNo());
            if(CollectionUtils.isNotEmpty(projectVO)){
                List<String> riskSubTypeList = Arrays.asList(projectDO.getRiskSubTypeCode().split(","));
                riskSubTypeList.forEach(risk->{
                    //查询模版数据
                    CgnciOutstandingClaimsReserveDO claimsReserveDOS = claimsReserveMapper.selectOne(new LambdaQueryWrapper<CgnciOutstandingClaimsReserveDO>()
                            .eq(CgnciOutstandingClaimsReserveDO::getRiskTypeCode,risk));
                    if(Objects.isNull(claimsReserveDOS)){
                        throw new GlobalException(ResponseCodeEnum.PARAMS_ILLEGAL, "险种代码："+risk+"的假设模版数据不存在，请导全数据");
                    }
                    CgnciOutstandingClaimsReserveLedgerDO reserveLedgerDO = new CgnciOutstandingClaimsReserveLedgerDO();
                    reserveLedgerDO.setRegisterId(extractDTO.getRegisterId());
                    reserveLedgerDO.setValuationDate(DateUtils.parseDate(extractDTO.getValuationMonth(),DateUtils.FORMAT_8));
                    reserveLedgerDO.setProjectName(projectDO.getProjectName());
                    reserveLedgerDO.setRiskClass(projectDO.getRiskClass());
                    reserveLedgerDO.setRiskSubType(claimsReserveDOS.getRiskType());
                    reserveLedgerDO.setDelayDays(claimsReserveDOS.getDelayDays());
                    reserveLedgerDO.setActualDelayDays(getActualDelayDays(claimsReserveDOS,projectDO,reserveLedgerDO.getValuationDate()));
                    reserveLedgerDO.setPolicyStartDate(projectDO.getStartDate());
                    reserveLedgerDO.setPolicyEndDate(projectDO.getEndDate());
                    reserveLedgerDO.setPolicyAnnual(projectDO.getProjectYear());
                    reserveLedgerDO.setPolicyCurrency(projectDO.getCurrency());
                    BigDecimal engagedCollect = new BigDecimal(0);
                    BigDecimal cededCollect = new BigDecimal(0);
                    BigDecimal inwardAmount = new BigDecimal(0);
                    BigDecimal recoveryAmount = new BigDecimal(0);
                    BigDecimal approvedReserve = new BigDecimal(0);
                    for(CgnciClaimPolicyVO project:projectVO){
                        //查询分入信息下的险类和险种
                        CgnciReinsuranceInfoVO infoVO = getDivideInReinsuranceByInsuranceId(project.getCurrentPolicyId());
                        // 转换为 Map<String, DTO>
                        Map<String, CgnciReinsuranceRiskVO> riskVOMap = infoVO.getRisks().stream()
                                .collect(Collectors.toMap(CgnciReinsuranceRiskVO::getRiskSubTypeCode, dto -> dto));
                        CgnciReinsuranceRiskVO riskVO = riskVOMap.get(risk);
                        BigDecimal epGross = new BigDecimal(0);
                        if(Objects.nonNull(riskVO)&&CollectionUtils.isNotEmpty(riskVO.getRiskInsureds())){
                            epGross = riskVO.getRiskInsureds().stream()
                                    .map(risk1 -> BeanUtils.sumBigDecimalProperty(risk1.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getGrossPremium))
                                    .reduce(BigDecimal.ZERO, BigDecimal::add);
                        }
                        engagedCollect = engagedCollect.add(epGross);
                        //分出险种信息
                        CgnciReinsuranceInfoVO lfDivideOutReinsuranceByInsuranceId = getLfDivideOutReinsuranceByInsuranceId(project.getCurrentPolicyId(),risk);
                        if(CollectionUtils.isEmpty(lfDivideOutReinsuranceByInsuranceId.getRisks())){
                            cededCollect = cededCollect.add(new BigDecimal(0));
                        }else{
                            List<CgnciReinsuranceRiskInsuredVO> riskLayerList = lfDivideOutReinsuranceByInsuranceId.getRisks().get(0).getRiskInsureds();
                            cededCollect = cededCollect.add(riskLayerList.stream()
                                    .map(riskLayer -> BeanUtils.sumBigDecimalProperty(riskLayer.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getGrossPremium))
                                    .reduce(BigDecimal.ZERO, BigDecimal::add));
                        }
                        List<CgnciClaimSettlementDetailDO> inwardDOS= claimSettlementDetailMapper.selectList(new LambdaQueryWrapper<CgnciClaimSettlementDetailDO>()
                                .eq(CgnciClaimSettlementDetailDO::getCaseNo,project.getCaseNo())
                                .eq(CgnciClaimSettlementDetailDO::getDetailType, ClaimDetailTypeEnum.INWARD.getCode())
                                 .eq(CgnciClaimSettlementDetailDO::getAmountType, ClaimAmountTypeEnum.COMPENSATION.getCode())
                                .eq(CgnciClaimSettlementDetailDO::getRiskType,risk));
                        if(CollectionUtils.isEmpty(inwardDOS)){
                            inwardAmount = inwardAmount.add(new BigDecimal(0));
                        }else{
                            inwardAmount = inwardAmount.add(BeanUtils.sumBigDecimalProperty(inwardDOS,CgnciClaimSettlementDetailDO::getSettlementAmount));
                        }
                        List<CgnciClaimSettlementDetailDO> recoveryDOS= claimSettlementDetailMapper.selectList(new LambdaQueryWrapper<CgnciClaimSettlementDetailDO>()
                                .eq(CgnciClaimSettlementDetailDO::getCaseNo,project.getCaseNo())
                                .eq(CgnciClaimSettlementDetailDO::getDetailType, ClaimDetailTypeEnum.RECOVERY.getCode())
                                .eq(CgnciClaimSettlementDetailDO::getAmountType, ClaimAmountTypeEnum.COMPENSATION.getCode())
                                .eq(CgnciClaimSettlementDetailDO::getRiskType,risk));
                        if(CollectionUtils.isEmpty(recoveryDOS)){
                            recoveryAmount = recoveryAmount.add(new BigDecimal(0));
                        }else{
                            recoveryAmount = recoveryAmount.add(BeanUtils.sumBigDecimalProperty(recoveryDOS,CgnciClaimSettlementDetailDO::getSettlementAmount));
                        }
                        //赔案准备金信息
                        List<CgnciClaimReserveDetailDO> detailDOS =  reserveDetailMapper.queryDetailByCaseNoAndRiskType(project.getCaseNo(),risk);
                        if(CollectionUtils.isEmpty(detailDOS)){
                            approvedReserve = approvedReserve.add(new BigDecimal(0));
                        }else{
                            approvedReserve = approvedReserve.add((BeanUtils.sumBigDecimalProperty(detailDOS,CgnciClaimReserveDetailDO::getApprovedReserve)));
                        }
                    }
                    BigDecimal epGross = engagedCollect.multiply(new BigDecimal(reserveLedgerDO.getActualDelayDays()));
                    Integer policyStartDays =DateUtils.getDiffDay(reserveLedgerDO.getPolicyStartDate(),reserveLedgerDO.getPolicyEndDate())+1;
                    reserveLedgerDO.setEpGross(epGross.divide(new BigDecimal(policyStartDays), 2, RoundingMode.HALF_UP));//5
                    BigDecimal epNet = cededCollect.multiply(new BigDecimal(reserveLedgerDO.getActualDelayDays()));
                    reserveLedgerDO.setEpNet(epNet.divide(new BigDecimal(policyStartDays), 2, RoundingMode.HALF_UP));//6
                    reserveLedgerDO.setLossRatio(claimsReserveDOS.getLrLossRatio());//7
                    reserveLedgerDO.setUlGross(reserveLedgerDO.getEpGross().multiply(reserveLedgerDO.getLossRatio())); //8
                    reserveLedgerDO.setUlNet(reserveLedgerDO.getEpNet().multiply(reserveLedgerDO.getLossRatio())); //9
                    reserveLedgerDO.setPaidGross(inwardAmount);//10
                    reserveLedgerDO.setPaidNet(recoveryAmount);//11
                    reserveLedgerDO.setCaseGross(approvedReserve);//12
                    reserveLedgerDO.setCaseNet(approvedReserve);//13
                    reserveLedgerDO.setIbnrGross(reserveLedgerDO.getUlGross().subtract(reserveLedgerDO.getPaidGross()).subtract(reserveLedgerDO.getCaseGross()));
                    reserveLedgerDO.setIbnrNet(reserveLedgerDO.getUlNet().subtract(reserveLedgerDO.getPaidNet()).subtract(reserveLedgerDO.getCaseNet()));
                    reserveLedgerDO.setUlaeExpenseRatio(claimsReserveDOS.getClaimsExpenseRatio());//16
                    BigDecimal ulaeExpense = new BigDecimal(0.5).multiply(reserveLedgerDO.getCaseGross()).add(reserveLedgerDO.getIbnrGross());
                    reserveLedgerDO.setUlaeExpense(ulaeExpense.multiply(reserveLedgerDO.getUlaeExpenseRatio()));//17
                    reserveLedgerDO.setRaRatio(claimsReserveDOS.getRiskAdjustedRate());//18
                    BigDecimal raGross = reserveLedgerDO.getCaseGross().add(reserveLedgerDO.getIbnrGross()).add(reserveLedgerDO.getUlaeExpense());
                    reserveLedgerDO.setRaGross(raGross.multiply(reserveLedgerDO.getRaRatio()));//19
                    BigDecimal raNet = reserveLedgerDO.getCaseNet().add(reserveLedgerDO.getIbnrNet()).add(reserveLedgerDO.getUlaeExpense());
                    reserveLedgerDO.setRaNet(raNet.multiply(reserveLedgerDO.getRaRatio()));
                    reserveLedgerDO.setGross(raGross.add(reserveLedgerDO.getRaGross()));
                    reserveLedgerDO.setNet(raNet.add(reserveLedgerDO.getRaNet()));
                    reserveLedgerDO.setCreateTime(new Date());
                    reserveLedgerDOS.add(reserveLedgerDO);
                });
            }
        });
        //批量分批插入数据
        batchInsertOutstanding(reserveLedgerDOS);
    }

    /**
     * 未到期保费准备金
     * @param extractDTO
     */
    public void unearned(ProvisionRegisterExtractDTO extractDTO) {
        LambdaQueryWrapper<CgnciBusinessInsuranceDO> query = new LambdaQueryWrapper<CgnciBusinessInsuranceDO>()
                .eq(CgnciBusinessInsuranceDO::getBusinessType,"1")
                .eq(CgnciBusinessInsuranceDO::getStatus,"3")
                .eq(CgnciBusinessInsuranceDO::getIsEffective, "1")
                .eq(CgnciBusinessInsuranceDO::getIsMirror, "0");
        //查询所有有效保单数据
        List<CgnciBusinessInsuranceDO> insuranceDOS = insuranceMapper.selectList(query);
        List<CgnciUnearnedPremiumReserveLedgerDO> reserveLedgerDOS = new ArrayList<>();
        insuranceDOS.forEach(insuranceDO->{
            CgnciEndorsementDO endorsementDO = endorsementMapper.selectOne(
                    new LambdaQueryWrapper<CgnciEndorsementDO>()
                            .eq(CgnciEndorsementDO::getBusinessNo, insuranceDO.getBusinessNo())
                            .orderByDesc(CgnciEndorsementDO::getCreateTime)
                            .last("LIMIT 1"));
            //查询分入信息下的险类和险种
            CgnciReinsuranceInfoVO infoVO = getDivideInReinsuranceByInsuranceId(insuranceDO.getInsuranceId());
            //以险种为维度
            infoVO.getRisks().forEach(risks->{
                //分入险种信息
                CgnciUnearnedPremiumReserveLedgerDO reserveLedgerDO = new CgnciUnearnedPremiumReserveLedgerDO();
                reserveLedgerDO.setPolicyNo(insuranceDO.getBusinessNo());
                reserveLedgerDO.setInsuredType(InsuredTypeEnum.getDescriptionByCode(risks.getRiskInsureds().get(0).getInsuredType()));
                reserveLedgerDO.setOffshoreTerritory(risks.getRiskInsureds().get(0).getOffshoreTerritory());
                reserveLedgerDO.setEndorsementNo(Objects.nonNull(endorsementDO)?endorsementDO.getEndorsementNo():null);
                reserveLedgerDO.setRegisterId(extractDTO.getRegisterId());
                reserveLedgerDO.setValuationDate(DateUtils.parseDate(extractDTO.getValuationMonth(),DateUtils.FORMAT_8));
                reserveLedgerDO.setProjectName(insuranceDO.getProjectName());
                reserveLedgerDO.setRiskClass(risks.getRiskClass());
                reserveLedgerDO.setRiskSubType(risks.getRiskSubType());
                reserveLedgerDO.setPolicyStartDate(insuranceDO.getStartDate());
                reserveLedgerDO.setPolicyEndDate(insuranceDO.getEndDate());
                List<CgnciReinsurancePremiumBillDO> premiumBillDOS =premiumBillMapper.selectList(new LambdaQueryWrapper<CgnciReinsurancePremiumBillDO>()
                        .eq(CgnciReinsurancePremiumBillDO::getPolicyNumber,insuranceDO.getBusinessNo())
                        .eq(CgnciReinsurancePremiumBillDO::getPolicyType,risks.getRiskSubTypeCode())
                        .eq(CgnciReinsurancePremiumBillDO::getDirection,"2"));
                if(CollectionUtils.isNotEmpty(premiumBillDOS)){
                    reserveLedgerDO.setPostingDate(premiumBillDOS.get(0).getPostingDate());
                }
                reserveLedgerDO.setPolicyCurrency(insuranceDO.getCurrency());
                reserveLedgerDO.setEngagedPremium(risks.getRiskInsureds().stream()
                        .map(risk -> BeanUtils.sumBigDecimalProperty(risk.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getGrossPremium))
                        .reduce(BigDecimal.ZERO, BigDecimal::add));
                //分出险种信息
                CgnciReinsuranceInfoVO lfDivideOutReinsuranceByInsuranceId = getLfDivideOutReinsuranceByInsuranceId(insuranceDO.getInsuranceId(), risks.getRiskClassCode());
                if(Objects.nonNull(lfDivideOutReinsuranceByInsuranceId)&&CollectionUtils.isNotEmpty(lfDivideOutReinsuranceByInsuranceId.getRisks())){
                    reserveLedgerDO.setCededPremium(risks.getRiskInsureds().stream()
                            .map(risk -> BeanUtils.sumBigDecimalProperty(risk.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getGrossPremium))
                            .reduce(BigDecimal.ZERO, BigDecimal::add));
                    //分出摊回手续费
                    reserveLedgerDO.setProcedurePremium(risks.getRiskInsureds().stream()
                            .map(risk -> BeanUtils.sumBigDecimalProperty(risk.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getCommission))
                            .reduce(BigDecimal.ZERO, BigDecimal::add));
                }else{
                    reserveLedgerDO.setCededPremium(new BigDecimal(0));
                    reserveLedgerDO.setProcedurePremium(new BigDecimal(0));
                }
                reserveLedgerDO.setNetPremium(reserveLedgerDO.getEngagedPremium().subtract(reserveLedgerDO.getCededPremium()));
                //分入手续费
                reserveLedgerDO.setReinsurancePremium(risks.getRiskInsureds().stream()
                        .map(risk -> BeanUtils.sumBigDecimalProperty(risk.getRiskLayers(), CgnciReinsuranceRiskLayerVO::getCommission))
                        .reduce(BigDecimal.ZERO, BigDecimal::add));
                reserveLedgerDO.setCommissionsFees(new BigDecimal(0));//目前没有此项费用
                reserveLedgerDO.setSubtotalPremium(reserveLedgerDO.getReinsurancePremium().subtract(reserveLedgerDO.getProcedurePremium()));
                //期初费用计算逻辑
                boolean isWithinRange = DateUtils.isDateInRange(reserveLedgerDO.getPolicyStartDate(),DateUtils.parseDate(extractDTO.getValuationYear(),DateUtils.FORMAT_8),reserveLedgerDO.getPolicyEndDate());
                boolean isBefore =false;
                if(reserveLedgerDO.getPostingDate()!=null){
                    isBefore = DateUtils.isEntryDateBeforeStartDate(reserveLedgerDO.getPostingDate(),DateUtils.parseDate(extractDTO.getValuationYear(),DateUtils.FORMAT_8));
                }
                //当期初日期在保险期限内（起保日期≤期初日期≤终保日期）且入账日期＜期初日期按计算公式计算，否则期初对应未到期责任准备金以及保单获取成本为 0
                if(isWithinRange && isBefore){
                   Integer valuationYearDays =  DateUtils.getDiffDay(DateUtils.parseDate(extractDTO.getValuationYear(),DateUtils.FORMAT_8),reserveLedgerDO.getPolicyEndDate());
                    Integer policyStartDays =DateUtils.getDiffDay(reserveLedgerDO.getPolicyStartDate(),reserveLedgerDO.getPolicyEndDate());
                    int initialDays =valuationYearDays / policyStartDays + 1;
                    reserveLedgerDO.setPolicyAcquireInitial(reserveLedgerDO.getEngagedPremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setReceivablesInitialUpr(reserveLedgerDO.getCededPremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setOriginalPolicyInitial(reserveLedgerDO.getReinsurancePremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setCededPolicyInitial(reserveLedgerDO.getCommissionsFees().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setReceivablesInitial(reserveLedgerDO.getProcedurePremium().multiply(new BigDecimal(initialDays)));
                }else{
                    reserveLedgerDO.setPolicyAcquireInitial(new BigDecimal(0));
                    reserveLedgerDO.setReceivablesInitialUpr(new BigDecimal(0));
                    reserveLedgerDO.setOriginalPolicyInitial(new BigDecimal(0));
                    reserveLedgerDO.setCededPolicyInitial(new BigDecimal(0));
                    reserveLedgerDO.setReceivablesInitial(new BigDecimal(0));
                }
                //期末费用计算逻辑
                boolean isEndTerm = DateUtils.isDateInRange(reserveLedgerDO.getPolicyStartDate(),DateUtils.parseDate(extractDTO.getValuationMonth(),DateUtils.FORMAT_8),reserveLedgerDO.getPolicyEndDate());
                //当期末日期在保险期限内（起保日期≤期末日期≤终保日期），按计算公式计算，否则期末对应未到期责任准备金以及保单获取成本为 0
                if(isEndTerm){
                    Integer valuationYearDays =  DateUtils.getDiffDay(DateUtils.parseDate(extractDTO.getValuationMonth(),DateUtils.FORMAT_8),reserveLedgerDO.getPolicyEndDate());
                    Integer policyStartDays =DateUtils.getDiffDay(reserveLedgerDO.getPolicyStartDate(),reserveLedgerDO.getPolicyEndDate());
                    int initialDays =valuationYearDays / policyStartDays + 1;
                    reserveLedgerDO.setPolicyAcquirePeriod(reserveLedgerDO.getEngagedPremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setReceivablesPeriodUpr(reserveLedgerDO.getCededPremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setOriginalPolicyPeriod(reserveLedgerDO.getReinsurancePremium().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setCededPolicyPeriod(reserveLedgerDO.getCommissionsFees().multiply(new BigDecimal(initialDays)));
                    reserveLedgerDO.setReceivablesPeriod(reserveLedgerDO.getProcedurePremium().multiply(new BigDecimal(initialDays)));
                }else{
                    reserveLedgerDO.setPolicyAcquirePeriod(new BigDecimal(0));
                    reserveLedgerDO.setReceivablesPeriodUpr(new BigDecimal(0));
                    reserveLedgerDO.setOriginalPolicyPeriod(new BigDecimal(0));
                    reserveLedgerDO.setCededPolicyPeriod(new BigDecimal(0));
                    reserveLedgerDO.setReceivablesPeriod(new BigDecimal(0));
                }
                // N=1-2-(3+4-5)
               BigDecimal initialAmount = reserveLedgerDO.getPolicyAcquireInitial().subtract(reserveLedgerDO.getReceivablesInitialUpr())
                       .subtract(reserveLedgerDO.getOriginalPolicyInitial().add(reserveLedgerDO.getCededPolicyInitial()).subtract(reserveLedgerDO.getReceivablesInitial()));
                reserveLedgerDO.setInitialAmount(initialAmount);
                BigDecimal periodAmount = reserveLedgerDO.getPolicyAcquirePeriod().subtract(reserveLedgerDO.getReceivablesPeriodUpr())
                        .subtract(reserveLedgerDO.getOriginalPolicyPeriod().add(reserveLedgerDO.getCededPolicyPeriod()).subtract(reserveLedgerDO.getReceivablesPeriod()));
                reserveLedgerDO.setPeriodAmount(periodAmount);
                reserveLedgerDO.setExtractionCessionDifference(reserveLedgerDO.getInitialAmount().subtract(reserveLedgerDO.getPeriodAmount()));
                reserveLedgerDO.setAccumulatedEarnedPremium(reserveLedgerDO.getNetPremium().subtract(reserveLedgerDO.getExtractionCessionDifference()));
                reserveLedgerDO.setUnderwritingProfit(reserveLedgerDO.getAccumulatedEarnedPremium().subtract(reserveLedgerDO.getSubtotalPremium()));
                reserveLedgerDO.setCededFeeRate(getCededFeeRate(insuranceDO.getInsuranceId()));
                reserveLedgerDO.setCashFlowRatio(new BigDecimal(0));//取数待确认
                reserveLedgerDO.setNonCashFlowRatio(new BigDecimal(0));//取数待确认
                reserveLedgerDOS.add(reserveLedgerDO);
            });
        });
        //批量分批插入数据
        batchInsertReserveLedger(reserveLedgerDOS);
    }

    /**
     * 未到期责任准备金保存
     * @param reserveLedgerDOS
     */
    public void batchInsertReserveLedger(List<CgnciUnearnedPremiumReserveLedgerDO> reserveLedgerDOS) {
        int batchSize = 500;
        int totalSize = reserveLedgerDOS.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;

        for (int i = 0; i < batchCount; i++) {
            int fromIndex = i * batchSize;
            int toIndex = Math.min(fromIndex + batchSize, totalSize);
            List<CgnciUnearnedPremiumReserveLedgerDO> batchList = reserveLedgerDOS.subList(fromIndex, toIndex);
            try {
                reserveLedgerService.saveBatch(batchList);
            } catch (Exception e) {
                throw e; // 抛出异常，回滚事务
            }
        }
    }

    /**
     * 未决赔款准备金保存
     * @param reserveLedgerDOS
     */
    public void batchInsertOutstanding(List<CgnciOutstandingClaimsReserveLedgerDO> reserveLedgerDOS) {
        int batchSize = 500;
        int totalSize = reserveLedgerDOS.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;

        for (int i = 0; i < batchCount; i++) {
            int fromIndex = i * batchSize;
            int toIndex = Math.min(fromIndex + batchSize, totalSize);
            List<CgnciOutstandingClaimsReserveLedgerDO> batchList = reserveLedgerDOS.subList(fromIndex, toIndex);
            try {
                outstandingClaimsService.saveBatch(batchList);
            } catch (Exception e) {
                throw e; // 抛出异常，回滚事务
            }
        }
    }
    /**
     * 计算实际报案延迟天数
     * @param claimsReserveDOS
     * @param projectDO
     * @return
     */
    private Double getActualDelayDays(CgnciOutstandingClaimsReserveDO claimsReserveDOS, CgnciBusinessProjectDO projectDO,Date valuationDate) {
        Integer days = DateUtils.getDiffDay(projectDO.getStartDate(),valuationDate);
        Date newDate = DateUtils.getAfterDateByDay(projectDO.getEndDate(),claimsReserveDOS.getDelayDays().intValue());
        if(days<claimsReserveDOS.getDelayDays()){
            return days.doubleValue();
        }
        if(DateUtils.isEntryDateBeforeStartDate(valuationDate,newDate)){
            return DateUtils.getDiffDay(valuationDate,newDate).doubleValue();
        }
        if(days>=claimsReserveDOS.getDelayDays() && DateUtils.lessThanEqualTo(newDate,valuationDate)){
            return claimsReserveDOS.getDelayDays();
        }
        return 0.0;
    }
    /**
     * 获取分出合同投资成分占比-纯溢手续费比例
     * @param insuranceId
     * @return
     */
    private BigDecimal getCededFeeRate(Long insuranceId){
        List<CgnciReinsuranceInfoDO> hydivideOutDOList = cgnciReinsuranceInfoService.list(new LambdaQueryWrapper<CgnciReinsuranceInfoDO>()
                .eq(CgnciReinsuranceInfoDO::getBusinessId, insuranceId)
                .eq(CgnciReinsuranceInfoDO::getReinsuranceType, 2));
        if(CollectionUtils.isEmpty(hydivideOutDOList)){
            return null;
        }
        List<CgnciReinsuranceInfoVO> hyDivideOutVOList = ConvertUtils.convert(hydivideOutDOList, CgnciReinsuranceInfoVO.class);
        // 返回合约分出编号
        String contractNo = hyDivideOutVOList.get(0).getContractNo();
        // 通过合约分出编号查询合约分出信息
        CgnciBusinessInsuranceDO divideOut = businessInsuranceService.getOne(new LambdaQueryWrapper<CgnciBusinessInsuranceDO>()
                .eq(CgnciBusinessInsuranceDO::getBusinessNo,contractNo)
                .eq(CgnciBusinessInsuranceDO::getIsMirror,"0"));
        if(Objects.isNull(divideOut)){
            return null;
        }
        CgnciBillInfoDO billInfoDO = billInfoService.getOne(new LambdaQueryWrapper<CgnciBillInfoDO>()
                .eq(CgnciBillInfoDO::getBusinessId, divideOut.getInsuranceId()));
        if(Objects.isNull(billInfoDO)){
            return null;
        }
        return billInfoDO.getPureCommissionRate();
    }

    /**
     * 查询分入信息下的险类和险种
     * @param insuranceId
     * @return
     */
    private CgnciReinsuranceInfoVO getDivideInReinsuranceByInsuranceId(Long insuranceId) {
        CgnciReinsuranceInfoDO divideInDO = infoMapper.selectOne(new LambdaQueryWrapper<CgnciReinsuranceInfoDO>()
                .eq(CgnciReinsuranceInfoDO::getBusinessId, insuranceId).eq(CgnciReinsuranceInfoDO::getReinsuranceType, 1));
        if (VerifyUtils.isNotEmpty(divideInDO)) {
            CgnciReinsuranceInfoVO divideInVO = ConvertUtils.convert(divideInDO, CgnciReinsuranceInfoVO.class);
            // 分入险种
            List<CgnciReinsuranceRiskDO> divideInRiskDOList = cgnciReinsuranceRiskService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskDO>()
                    .eq(CgnciReinsuranceRiskDO::getReinsuranceId, divideInDO.getReinsuranceId()));
            if (VerifyUtils.isNotEmpty(divideInRiskDOList)) {
                List<CgnciReinsuranceRiskVO> divideInRiskVOList = ConvertUtils.convert(divideInRiskDOList, CgnciReinsuranceRiskVO.class);
                List<Long> reinsuranceRiskIds = divideInRiskVOList.stream().map(CgnciReinsuranceRiskVO::getReinsuranceRiskId).collect(Collectors.toList());

                Map<Long, List<CgnciReinsuranceRiskInsuredVO>> reinsuranceRiskIdMap = new HashMap<>();
                Map<Long, List<CgnciReinsuranceRiskLayerVO>> reinsuranceRiskInsuredIdMap = new HashMap<>();

                // 分入险种标的
                List<CgnciReinsuranceRiskInsuredDO> divideInRiskInsuredDOList = cgnciReinsuranceRiskInsuredService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskInsuredDO>()
                        .in(CgnciReinsuranceRiskInsuredDO::getReinsuranceRiskId, reinsuranceRiskIds));
                if (VerifyUtils.isNotEmpty(divideInRiskInsuredDOList)) {
                    List<CgnciReinsuranceRiskInsuredVO> divideInRiskInsuredVOList = ConvertUtils.convert(divideInRiskInsuredDOList, CgnciReinsuranceRiskInsuredVO.class);
                    // 分入险种标的ID集合
                    List<Long> riskInsuredIds = divideInRiskInsuredVOList.stream().map(CgnciReinsuranceRiskInsuredVO::getRiskInsuredId).collect(Collectors.toList());
                    // 分入险种标的根据分入险种id分组
                    reinsuranceRiskIdMap = divideInRiskInsuredVOList.stream().collect(Collectors.groupingBy(CgnciReinsuranceRiskInsuredVO::getReinsuranceRiskId));

                    // 分入险种层
                    List<CgnciReinsuranceRiskLayerDO> divideInRiskLayerDOList = cgnciReinsuranceRiskLayerService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskLayerDO>()
                            .in(CgnciReinsuranceRiskLayerDO::getReinsuranceRiskId, riskInsuredIds));
                    if (VerifyUtils.isNotEmpty(divideInRiskLayerDOList)) {
                        List<CgnciReinsuranceRiskLayerVO> divideInRiskLayerVOList = ConvertUtils.convert(divideInRiskLayerDOList, CgnciReinsuranceRiskLayerVO.class);
                        // 分入险种层根据分入险种标的分组
                        reinsuranceRiskInsuredIdMap = divideInRiskLayerVOList.stream().collect(Collectors.groupingBy(CgnciReinsuranceRiskLayerVO::getReinsuranceRiskId));
                    }
                }

                // 组装VO
                Map<Long, List<CgnciReinsuranceRiskInsuredVO>> finalRiskIdMap = reinsuranceRiskIdMap;
                Map<Long, List<CgnciReinsuranceRiskLayerVO>> finalRiskInsuredIdMap = reinsuranceRiskInsuredIdMap;
                divideInRiskVOList.forEach(risk -> {
                    List<CgnciReinsuranceRiskInsuredVO> riskInsuredVOS = finalRiskIdMap.get(risk.getReinsuranceRiskId());
                    riskInsuredVOS.forEach(riskInsured -> {
                        List<CgnciReinsuranceRiskLayerVO> riskLayerVOS = finalRiskInsuredIdMap.get(riskInsured.getRiskInsuredId());
                        riskInsured.setRiskLayers(riskLayerVOS);
                    });
                    risk.setRiskInsureds(riskInsuredVOS);
                });
                divideInVO.setRisks(divideInRiskVOList);
            }
            return divideInVO;
        }
        return null;
    }

    /**
     * 查询分出险种信息
     * @param insuranceId
     * @param riskClassCode
     * @return
     */
    private CgnciReinsuranceInfoVO getLfDivideOutReinsuranceByInsuranceId(Long insuranceId,String riskClassCode) {
        CgnciReinsuranceInfoDO lfDivideOutDO = infoMapper.selectOne(new LambdaQueryWrapper<CgnciReinsuranceInfoDO>()
                .eq(CgnciReinsuranceInfoDO::getBusinessId, insuranceId).eq(CgnciReinsuranceInfoDO::getReinsuranceType, 3));
        if (VerifyUtils.isNotEmpty(lfDivideOutDO)) {
            CgnciReinsuranceInfoVO lfDivideOutVO = ConvertUtils.convert(lfDivideOutDO, CgnciReinsuranceInfoVO.class);
            List<CgnciReinsuranceRiskDO> lfDivideOutRiskDOList = cgnciReinsuranceRiskService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskDO>()
                    .eq(CgnciReinsuranceRiskDO::getReinsuranceId, lfDivideOutDO.getReinsuranceId())
                    .eq(CgnciReinsuranceRiskDO::getRiskClassCode, riskClassCode));
            if (VerifyUtils.isNotEmpty(lfDivideOutRiskDOList)) {
                List<CgnciReinsuranceRiskVO> lfDivideOutRiskRiskVOList = ConvertUtils.convert(lfDivideOutRiskDOList, CgnciReinsuranceRiskVO.class);
                List<Long> reinsuranceRiskIds = lfDivideOutRiskRiskVOList.stream().map(CgnciReinsuranceRiskVO::getReinsuranceRiskId).collect(Collectors.toList());

                Map<Long, List<CgnciReinsuranceRiskInsuredVO>> reinsuranceRiskIdMap = new HashMap<>();
                Map<Long, List<CgnciReinsuranceRiskLayerVO>> reinsuranceRiskInsuredIdMap = new HashMap<>();

                // 分出险种标的
                List<CgnciReinsuranceRiskInsuredDO> lfDivideOutRiskInsuredDOList = cgnciReinsuranceRiskInsuredService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskInsuredDO>()
                        .in(CgnciReinsuranceRiskInsuredDO::getReinsuranceRiskId, reinsuranceRiskIds));
                if (VerifyUtils.isNotEmpty(lfDivideOutRiskInsuredDOList)) {
                    List<CgnciReinsuranceRiskInsuredVO> lfDivideOutRiskInsuredVOList = ConvertUtils.convert(lfDivideOutRiskInsuredDOList, CgnciReinsuranceRiskInsuredVO.class);
                    // 临分分出险种标的ID集合
                    List<Long> riskInsuredIds = lfDivideOutRiskInsuredVOList.stream().map(CgnciReinsuranceRiskInsuredVO::getRiskInsuredId).collect(Collectors.toList());
                    // 分入险种标的根据分入险种id分组
                    reinsuranceRiskIdMap = lfDivideOutRiskInsuredVOList.stream().collect(Collectors.groupingBy(CgnciReinsuranceRiskInsuredVO::getReinsuranceRiskId));

                    // 临分分出险种层
                    List<CgnciReinsuranceRiskLayerDO> lfDivideOutRiskLayerDOList = cgnciReinsuranceRiskLayerService.list(new LambdaQueryWrapper<CgnciReinsuranceRiskLayerDO>()
                            .in(CgnciReinsuranceRiskLayerDO::getReinsuranceRiskId, riskInsuredIds));
                    if (VerifyUtils.isNotEmpty(lfDivideOutRiskLayerDOList)) {
                        List<CgnciReinsuranceRiskLayerVO> lfDivideOutRiskLayerVOList = ConvertUtils.convert(lfDivideOutRiskLayerDOList, CgnciReinsuranceRiskLayerVO.class);
                        // 分入险种层根据分入险种标的分组
                        reinsuranceRiskInsuredIdMap = lfDivideOutRiskLayerVOList.stream().collect(Collectors.groupingBy(CgnciReinsuranceRiskLayerVO::getReinsuranceRiskId));
                    }
                }

                // 组装VO
                Map<Long, List<CgnciReinsuranceRiskInsuredVO>> finalReinsuranceRiskIdMap = reinsuranceRiskIdMap;
                Map<Long, List<CgnciReinsuranceRiskLayerVO>> finalReinsuranceRiskInsuredIdMap = reinsuranceRiskInsuredIdMap;
                lfDivideOutRiskRiskVOList.forEach(risk -> {
                    List<CgnciReinsuranceRiskInsuredVO> riskInsuredVOS = finalReinsuranceRiskIdMap.get(risk.getReinsuranceRiskId());
                    riskInsuredVOS.forEach(riskInsured -> {
                        List<CgnciReinsuranceRiskLayerVO> riskLayerVOS = finalReinsuranceRiskInsuredIdMap.get(riskInsured.getRiskInsuredId());
                        riskInsured.setRiskLayers(riskLayerVOS);
                    });
                    risk.setRiskInsureds(riskInsuredVOS);
                });
                lfDivideOutVO.setRisks(lfDivideOutRiskRiskVOList);
            }
            return lfDivideOutVO;
        }
        return null;
    }
    /**
     * 提取数据下载文件
     *
     * @param registerId
     * @return
     */
    @Override
    public ExcelWriter export(Long registerId, String reserveTypeCode) {
        switch (reserveTypeCode) {
            case OUTSTANDING:
                return OutstandingClaimsReserveLedger(registerId,null);
            case UNEARNED:
                return UnearnedPremiumReserveLedger(registerId,null);
            default:
                throw new GlobalException(ResponseCodeEnum.HTTP_REQUEST_SERVICE_ERROR);
        }
    }

    /**
     * 提取未决赔款准备金台账文件
     *
     * @param registerId
     * @return ExcelWriter
     */
    @Override
    public ExcelWriter OutstandingClaimsReserveLedger(Long registerId, CgnciUnearnedPremiumReserveLedgerDTO queryDTO) {
        List<CgnciOutstandingClaimsReserveLedgerDO> reserveLedgerDOS = new ArrayList<>();
        if(registerId!=null) {
           reserveLedgerDOS = outstandingClaimsReserveLedgerMapper.selectList(new LambdaQueryWrapper<CgnciOutstandingClaimsReserveLedgerDO>()
                    .eq(CgnciOutstandingClaimsReserveLedgerDO::getRegisterId, registerId));
        }else{
            IPage<CgnciOutstandingClaimsReserveLedgerVO> listData = outstandingClaimsReserveLedgerMapper.page(queryDTO.toPage(),queryDTO);
            reserveLedgerDOS = ConvertUtils.convert(listData.getRecords(),CgnciOutstandingClaimsReserveLedgerDO.class);
        }
        ExcelWriter writer = ExcelUtil.getWriter(true);
        try {
            List<String> title = CollUtil.newArrayList(
                    "评估日期",
                    "项目名称",
                    "险种大类",
                    "险种",
                    "报案延迟天数",
                    "实际报案延迟天数",
                    "起保日期",
                    "终保日期",
                    "保单年",
                    "币种",
                    "EP（Gross）-已赚保费",
                    "EP（Net）-已赚保费",
                    "LR%-损失率",
                    "UL（Gross）-最终赔付额",
                    "UL（Net）-最终赔付额",
                    "Paid（Gross）-已付赔款",
                    "Paid（Net）-已付赔款",
                    "Case（Gross）-个案准备金",
                    "Case（Net）-个案准备金",
                    "IBNR（Gross）-已发生未报案赔款准备金",
                    "IBNR（Net）-已发生未报案赔款准备金",
                    "ULAE%-间接理赔费用率",
                    "ULAE-间接理赔费用",
                    "RA%-风险调整费率",
                    "RA（Gross）-风险调整",
                    "RA（Net）-风险调整",
                    "未决赔款准备金（Gross）（原币）",
                    "未决赔款准备金（Net）（原币）");
            writer.writeHeadRow(title);
            for (CgnciOutstandingClaimsReserveLedgerDO reserveLedgerDO : reserveLedgerDOS) {
                writer.writeRow(Arrays.asList(
                        DateUtils.formatDate(reserveLedgerDO.getValuationDate(), DateUtils.FORMAT_3),
                        reserveLedgerDO.getProjectName(),
                        reserveLedgerDO.getRiskClass(),
                        reserveLedgerDO.getRiskSubType(),
                        reserveLedgerDO.getDelayDays(),
                        reserveLedgerDO.getActualDelayDays(),
                        DateUtils.formatDate(reserveLedgerDO.getPolicyStartDate(), DateUtils.FORMAT_3),
                        DateUtils.formatDate(reserveLedgerDO.getPolicyEndDate(), DateUtils.FORMAT_3),
                        reserveLedgerDO.getPolicyAnnual(),
                        reserveLedgerDO.getPolicyCurrency(),
                        reserveLedgerDO.getEpGross(),
                        reserveLedgerDO.getEpNet(),
                        reserveLedgerDO.getLossRatio()+"%",
                        reserveLedgerDO.getUlGross(),
                        reserveLedgerDO.getUlNet(),
                        reserveLedgerDO.getPaidGross(),
                        reserveLedgerDO.getPaidNet(),
                        reserveLedgerDO.getCaseGross(),
                        reserveLedgerDO.getCaseNet(),
                        reserveLedgerDO.getIbnrGross(),
                        reserveLedgerDO.getIbnrNet(),
                        reserveLedgerDO.getUlaeExpenseRatio()+"%",
                        reserveLedgerDO.getUlaeExpense(),
                        reserveLedgerDO.getRaRatio()+"%",
                        reserveLedgerDO.getRaGross(),
                        reserveLedgerDO.getRaNet(),
                        reserveLedgerDO.getGross(),
                        reserveLedgerDO.getNet()
                ));
            }
        } catch (Exception e) {
            log.error("导出模版失败", e);
            throw new GlobalException(ResponseCodeEnum.PARAMS_ILLEGAL, "导出模版有误");
        }
        return writer;
    }

    /**
     * 提取未到期责任准备金台账文件
     *
     * @param registerId
     * @return ExcelWriter
     */
    @Override
    public  ExcelWriter UnearnedPremiumReserveLedger(Long registerId,CgnciUnearnedPremiumReserveLedgerDTO queryDTO) {

        ExcelWriter writer = ExcelUtil.getWriter(true);
        try {
            // 设置表头
            List<String> titleRow1 = CollUtil.newArrayList(
                    "期初日期",
                    "期末日期"
            );
            // 写入第一行标题
            writer.writeRow(titleRow1, true);
            List<CgnciUnearnedPremiumReserveLedgerDO> reserveLedgerDOS =new ArrayList<>();
            if (VerifyUtils.isEmpty(registerId)){
                IPage<CgnciUnearnedPremiumReserveLedgerVO> ledgerVOIPage =  unearnedPremiumReserveLedgerMapper.page(queryDTO.toPage(),queryDTO);
                reserveLedgerDOS = ConvertUtils.convert(ledgerVOIPage.getRecords(),CgnciUnearnedPremiumReserveLedgerDO.class);
                List<String> titleRow2 = CollUtil.newArrayList(
                        "-",
                        "-"
                );
                // 写入第二行日期数据
                writer.writeRow(titleRow2, false);
            }else{
                CgnciProvisionRegisterDO registerDO = this.baseMapper.selectById(registerId);
                reserveLedgerDOS =  unearnedPremiumReserveLedgerMapper.selectList(new LambdaQueryWrapper<CgnciUnearnedPremiumReserveLedgerDO>()
                        .eq(CgnciUnearnedPremiumReserveLedgerDO::getRegisterId,registerId));
                List<String> titleRow2 = CollUtil.newArrayList(
                        registerDO.getInitialDate(),
                        registerDO.getPeriodDate()
                );
                // 写入第二行日期数据
                writer.writeRow(titleRow2, false);

            }
            List<String> titleRow3 = CollUtil.newArrayList(
                    "保单号",
                    "批单号",
                    "离/在岸",
                    "离岸业务归属地",
                    "险种大类",
                    "险种",
                    "起保日期",
                    "终保日期",
                    "入账日期",
                    "币种",
                    "(分入)保费收入",
                    "分出保费",
                    "应计入本期净保费",
                    "分保费用","佣金及手续费","摊回分保费用","小计",
                    "原保单提取未到期责任准备金","应收分保未到期责任准备金","原保单递延保单获取成本","分出保单递延获取成本","应收分保递延获取成本",
                    "原保单提取未到期责任准备金","应收分保未到期责任准备金","原保单递延保单获取成本","分出保单递延获取成本","应收分保递延获取成本",
                    "期初数","期末数(BS)","提转差",
                    "累计已赚保费(会计年度)（原币）",
                    "当期业务成本",
                    "承保利润（原币）",
                    "分入合同投资成分占比",
                    "分出合同投资成分占比",
                    "跟单保险获取现金流比例",
                    "非跟单保险获取现金流比例"
            );
            // 写入第三行日期数据
            writer.writeRow(titleRow3, false);
            // 合并单元格
            CellStyle style = writer.createCellStyle();
            // 设置单元格内文字的对齐方式为居中
            style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            mergeExcel(writer,style);
           for(CgnciUnearnedPremiumReserveLedgerDO reserveLedgerDO: reserveLedgerDOS){
               writer.writeRow(Arrays.asList(
                       reserveLedgerDO.getPolicyNo(),
                       reserveLedgerDO.getEndorsementNo(),
                       reserveLedgerDO.getInsuredType(),
                       reserveLedgerDO.getOffshoreTerritory(),
                       reserveLedgerDO.getRiskClass(),
                       reserveLedgerDO.getRiskSubType(),
                       DateUtils.formatDate(reserveLedgerDO.getPolicyStartDate(),DateUtils.FORMAT_3),
                       DateUtils.formatDate(reserveLedgerDO.getPolicyEndDate(),DateUtils.FORMAT_3),
                       DateUtils.formatDate(reserveLedgerDO.getPostingDate(),DateUtils.FORMAT_3),
                       reserveLedgerDO.getPolicyCurrency(),
                       reserveLedgerDO.getEngagedPremium(),
                       reserveLedgerDO.getCededPremium(),
                       reserveLedgerDO.getNetPremium(),
                       reserveLedgerDO.getReinsurancePremium(),
                       reserveLedgerDO.getCommissionsFees(),
                       reserveLedgerDO.getProcedurePremium(),
                       reserveLedgerDO.getSubtotalPremium(),
                       reserveLedgerDO.getPolicyAcquireInitial(),
                       reserveLedgerDO.getReceivablesInitialUpr(),
                       reserveLedgerDO.getOriginalPolicyInitial(),
                       reserveLedgerDO.getCededPolicyInitial(),
                       reserveLedgerDO.getReceivablesInitial(),
                       reserveLedgerDO.getPolicyAcquirePeriod(),
                       reserveLedgerDO.getReceivablesPeriodUpr(),
                       reserveLedgerDO.getOriginalPolicyPeriod(),
                       reserveLedgerDO.getCededPolicyPeriod(),
                       reserveLedgerDO.getReceivablesPeriod(),
                       reserveLedgerDO.getInitialAmount(),
                       reserveLedgerDO.getPeriodAmount(),
                       reserveLedgerDO.getExtractionCessionDifference(),
                       reserveLedgerDO.getAccumulatedEarnedPremium(),
                       reserveLedgerDO.getSubtotalPremium(),
                       reserveLedgerDO.getUnderwritingProfit(),
                       reserveLedgerDO.getEngagedFeeRate(),
                       reserveLedgerDO.getCededFeeRate(),
                       reserveLedgerDO.getCashFlowRatio(),
                       reserveLedgerDO.getNonCashFlowRatio()
               ));
           }
        } catch (Exception e) {
            log.error("导出数据失败", e);
            throw new GlobalException(ResponseCodeEnum.PARAMS_ILLEGAL, "导出数据失败"+e);
        }
        return writer;
    }

    private void mergeExcel(ExcelWriter writer, CellStyle style) {
//        int startCol = 0; // 起始列
//        int endCol = 9;  // 结束列
//        for (int col = startCol; col <= endCol; col++) {
//            writer.merge(2, 4, col, col, null, style);
//        }
//        startCol = 27; // 起始列
//        endCol = 32;  // 结束列
//        for (int col = startCol; col <= endCol; col++) {
//            writer.merge(2, 4, col, col, null, style);
//        }
//        startCol = 10; // 起始列
//        endCol = 26;  // 结束列
//        for (int col = startCol; col <= endCol; col++) {
//            writer.merge(3, 4, col, col, null, style);
//        }
        writer.merge(1,1,13,16,"保单获取成本",style);
        writer.merge(1,1,17,21,"期初",style);
        writer.merge(1,1,22,26,"期末",style);
        writer.merge(1,1,27,29,"未到期责任准备金",style);
        //writer.merge(2,3,28,33,null,style);

    }


}
